import pytz
from datetime import date, timedelta, datetime
from dateutil.relativedelta import relativedelta

from odoo import models, api, fields
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT, DEFAULT_SERVER_DATE_FORMAT


class SPlanAchievementRateReport(models.AbstractModel):
    _name = 's.plan.achievement.rate.report'
    _description = 'Plan Achievement Rate Report'

    @api.model
    def get_achievement_rate(self, s_date=None, date_type='week'):
        utc_now = datetime.utcnow()
        target_tz = pytz.timezone(self._context.get('tz'))
        local_time = utc_now.replace(tzinfo=pytz.utc).astimezone(target_tz)
        to_day = local_time.date()
        if not s_date:
            s_date = to_day
        else:
            s_date = datetime.strptime(s_date, DEFAULT_SERVER_DATE_FORMAT)
            s_date = fields.Date.to_date(s_date)

        if date_type == 'month':
            start_date = s_date.replace(day=1)
            end_date = start_date + relativedelta(months=1) - timedelta(days=1)
        else:
            start_date = s_date - timedelta(days=s_date.weekday())
            end_date = start_date + timedelta(days=6)

        sql = f"""
            SELECT smt.station_id,
                   smt.planned_completion_date       AS date,
                   -- 计算达成率（done任务数 / 总任务数），使用NULLIF避免除数为0
                   round(COUNT(CASE WHEN smt.state = 'done' THEN 1 END) * 1.0 /
                         NULLIF(COUNT(*), 0), 4) * 100 AS achievement_rate
            FROM s_mrp_task smt
            left join work_station ws on smt.station_id = ws.id
            where ws.disuse is not true
              and smt.state in ('done', 'fail')
              and smt.planned_completion_date >= '{start_date.strftime(DEFAULT_SERVER_DATE_FORMAT)}'
              and smt.planned_completion_date <= '{end_date.strftime(DEFAULT_SERVER_DATE_FORMAT)}'
            GROUP BY station_id, planned_completion_date
            ORDER BY station_id, planned_completion_date;
        """

        self._cr.execute(sql)
        result = self._cr.fetchall()
        # 创建工站数据映射
        station_data = {}
        for row in result:
            station_id, planned_completion_date, achievement_rate = row
            key = (station_id, planned_completion_date)
            station_data[key] = achievement_rate

        lines = []
        work_station_ids = self.env['work.station'].search([('disuse', '=', False)])
        for station in work_station_ids:
            daily_output = [station.name]
            current_date = start_date
            while current_date <= end_date:
                station_rate = station_data.get((station.id, current_date), 0)
                daily_output.append(station_rate)
                current_date += timedelta(days=1)

            lines.append(daily_output)

        return {
            'header': self.get_header(start_date, end_date),  # 调用之前实现的get_header方法
            'lines': lines,
            'dateStr': f'{start_date.strftime(DEFAULT_SERVER_DATE_FORMAT)} to {end_date.strftime(DEFAULT_SERVER_DATE_FORMAT)}'
        }

    @api.model
    def get_header(self, start_date=None, end_date=None):
        header = ['工站']
        week_dates = []
        while True:
            week_dates.append(start_date)
            start_date = start_date + timedelta(days=1)
            if start_date > end_date:
                break

        # 格式化为"月/日"的字符串列表
        week_list = [f"{d.month}/{d.day}" for d in week_dates]

        return header + week_list
